# 2021-8-27 10:07 合并 by grx
from django.core import signing
from rest_framework.decorators import api_view
from questionnaire.models import *
from django.http import FileResponse, HttpResponse
from django.db.models import Avg, Max, Min, Count, Sum
from rest_framework.response import Response
from rest_framework import status
from questionnaire.views_statistics import *

from docx.shared import Pt, RGBColor
from docx import Document
from docx.oxml.ns import qn
from os.path import *

from docx.shared import Pt, RGBColor
from docx import Document
from docx.oxml.ns import qn


from djangoProject.settings import *

import math
import xlwt
import io
import json

from questionnaire.views_statistics import *


# 导出全部原始数据到excel
# 问卷中所有问题id，提交者信息列表
def idToTitle(wid):
    # 建立字典，键：值 = 问题id ： 问题题干
    d = {}
    questions = Question.objects.filter(wjId=wid)
    for q in questions:
        d[q.id] = q.title
    return d


def getContentOfGrade(ans):
    # 检查答案是否为空、是否必填
    print('grade')
    print(ans, ans.answer)
    if ans.answer is None:
        q = Question.objects.filter(id=ans.questionId).first()
        if q is None or q.must:
            return -1
        else:
            return ""
    return str(ans.answer)


# 多选题的字段之间用【;】连接 ls中存放Answer
def getContentOfMulti(ls, q_must):
    # 检查答案是否为空、是否必填
    if ls is None:
        if q_must is None or q_must.must:
            return -1
        else:
            return ""
    else:
        tmp = []
        for item in ls:
            opt = Options.objects.filter(id=item.answer).first()
            if opt is None:
                return -2
            else:
                tmp.append(opt.title)
    semi = ';'
    return semi.join(tmp)


def getContentOfSingle(ans):
    # 检查答案是否为空、是否必填
    if ans.answer is None:
        q_must = Question.objects.filter(id=ans.questionId).first()
        if q_must is None or q_must.must:
            return -1
        else:
            return ""
    else:
        opt = Options.objects.filter(id=ans.answer).first()
        if opt is None:
            return -2
        else:
            return opt.title


@api_view(['POST', ])
# 找到所有问卷id为qid的回答，整合成excel表格保存在指定/默认路径
def exportRawData(request):
    questionnaire_id = request.data.get('qid')
    # 判断qid是否有效
    if questionnaire_id:
        # 找出所有有效提交的submitId
        submitted_id = list(Submit.objects.filter(wjId=questionnaire_id).filter(is_submitted=True).values_list('id', flat=True))
        submitted_id.sort()
        rsl_answer = Answer.objects.filter(wjId=questionnaire_id).filter(submitId__in=submitted_id).order_by('submitId')
        rsl_submit = Submit.objects.filter(wjId=questionnaire_id).filter(is_submitted=True).order_by('id')

        # 写入excel文件
        # filename = 'media//' + str(questionnaire_id) + '.xls'
        value = signing.dumps({'questionnaire_id': questionnaire_id})
        filename = 'media/' + value + '.xls'
        write_book = xlwt.Workbook(encoding='utf-8')  # 打开一个excel
        sheet = write_book.add_sheet('data', cell_overwrite_ok=True)  # 在打开的excel中添加一个sheet

        # 获取表格行标题并填入表格,getHeaderLine函数返回表格行标题列表、来自Answer表的列数、来自Submit表的列数
        l_a = list(Question.objects.filter(wjId=questionnaire_id).values_list('id', flat=True).distinct())     # l_a 为所有题目的标题的id
        # l_a = list(Question.objects.filter(wjId=questionnaire_id).filter())
        l_s = ['用户名', '提交时间', '填写用时/秒']
        dict_question = idToTitle(questionnaire_id)
        header_line = ['序号']
        header_line.extend(l_a)
        header_line.extend(l_s)
        ans, sub = len(l_a), len(l_s)
        # 写入行标题：
        # ['序号', 'question's id', ..., '用户名', '提交时间', '提交ip', '填写用时/秒']
        # ['', 'question's title', ..., '', '', '', '']
        for i in range(0, len(header_line)):
            sheet.write(0, i, str(header_line[i]))
            if 0 < i <= ans:    # if header_line[i] in l_a:
                sheet.write(1, i, dict_question[header_line[i]])
            else:
                sheet.write(1, i, '-')

        # 填表格列标题
        for j in range(0, len(submitted_id)):
            sheet.write(j + 2, 0, submitted_id[j])

        # 暴力填充表格：answer
        multi_l = []     # 记录已经处理过的多选题
        tmp = []        # 暂时记录某一答者对某一多选题的全部作答
        for q in rsl_answer:
            # 查询q所应在的行和列
            print('q.submitId = ' + str(q.submitId))
            print('submitted_id = ' + str(submitted_id))
            print(dict_question)
            print('q.id=' + str(q.id) + ',submitId=' + str(q.submitId) + ',wjId=' + str(q.wjId) + ',qid = ' + str(q.questionId))
            if (q.questionId in header_line) and (q.submitId in submitted_id):     # and (dict_question[q.questionId] in header_line):
                i = submitted_id.index(q.submitId) + 2
                j = header_line.index(q.questionId)
                if q.type == 'fill':
                    sheet.write(i, j, q.answerText)
                elif q.type == 'grade':
                    content = getContentOfGrade(q)  # return str
                    sheet.write(i, j, content)
                elif q.type == 'multi':
                    if q.questionId in multi_l:
                        tmp.append(q)
                    else:
                        tmp = [q]
                        multi_l.append(q.questionId)
                    # 每次都重新写入
                    content = getContentOfMulti(tmp, Question.objects.filter(id=q.questionId).first())  # return str
                    sheet.write(i, j, content)
                elif q.type == 'single':
                    content = getContentOfSingle(q)  # return str
                    sheet.write(i, j, content)

        # 填充submit表
        for q in rsl_submit:
            # 查询q所应在的行
            i = submitted_id.index(q.id) + 2
            # 数据类型转化
            if q.username is None:
                sheet.write(i, ans + 1, '')
            else:
                sheet.write(i, ans + 1, q.username)
            sheet.write(i, ans + 2, (q.submitTime + datetime.timedelta(hours=8)).__str__()[:-6])
            # sheet.write(i, ans + 3, q.submitIp)
            sheet.write(i, ans + 3, str(q.useTime))

        sheet.write(len(submitted_id) + 3, 0, '备注：评分题对应内容若显示为’-1‘，则意为该题为必填题，但答者未作答')

        write_book.save(filename)
        response = {
            'code': 0,
            'msg': 'msg',
            # 'path': DEFAULT_URL + 'media/' + str(questionnaire_id) + '.xls'
            'path': DEFAULT_URL + 'media/' + value + '.xls'
        }
    else:
        response = {
            'code': 1,
            'msg': 'qid无效或为空'
        }
    return Response(response, status=status.HTTP_200_OK)


# # 导出统计数据到excel
# # 填写问卷总人数统计
def totalQuestionnaire(wid):
    res = Submit.objects.filter(wjId=wid).filter(is_submitted=True).aggregate(c=Count("id"))
    # 计算成功返回对应数字，失败则返回’-‘
    if res:
        return res['c']
    else:
        return '-'


# 填写问卷平均时间
def avgTimeToQuestionnaire_ex(wid):
    res = Submit.objects.filter(wjId=wid).filter(is_submitted=True).aggregate(a=Avg("useTime"))
    # 计算成功则返回对应数值字符串，失败则返回‘-’
    if res and res['a']:
        t = res['a']
        return "%.2f" % t
    return '-'


# 直方图统计
# 计算各组样本数
def countSample(ls, inf, sup):
    s = 0
    ls.sort()
    for item in ls:
        if item < inf:
            continue
        elif item >= sup:
            break
        s += 1
    return s


def timeHistogram(wid):
    res = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('useTime', flat=True))
    res.sort()
    if len(res) == 0:
        return '-'
    # 获取上下限，并计算出极差
    r = res[-1] - res[0]
    k = math.floor(math.sqrt(r)) + 1
    d = r // k
    dict_his = {}
    for i in range(k):
        if i == k - 1:
            dict_his[str((res[0] + i * d, res[0] + (i + 1) * d))] = countSample(res, res[0] + i * d, res[-1] + 1)
        else:
            dict_his[str((res[0] + i * d, res[0] + (i + 1) * d))] = countSample(res, res[0] + i * d, res[0] + (i + 1) * d)
    return dict_his


# 计算某道题的填写率
def getFillingRate(q, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    total = Submit.objects.filter(wjId=q.wjId).filter(is_submitted=True).count()
    # 填写率有意义时，计算数值并返回;无意义时，返回’-‘(页面显示’-%‘)
    if total != 0:
        if q.type == 'fill':
            ans = Answer.objects.filter(questionId=q.id).filter(submitId__in=ls_submitted).exclude(answerText="").exclude(answerText=None).count()
        else:
            ans = len(Answer.objects.filter(questionId=q.id).filter(submitId__in=ls_submitted).exclude(answer=None).values_list('submitId', flat=True).distinct())
        return "%.2f" % ((ans * 100) / total)
    else:
        return '-'


# 选择题统计
# 统计单个问题各个选项的选择人数分布
def optionCount(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    # 找出所有回答
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted)
    # 找出全部的选项
    opt = Options.objects.filter(questionId=qid)
    dict_opt = {}
    # 初始化, 键：值 = 选项id:[选项内容, 选择人数, 选择比例]
    for item in opt:
        dict_opt[item.id] = [item.title, 0, '']
    for a in ans:
        # 对异常的选择结果进行忽略处理
        if a and (a.answer in dict_opt.keys()):
            # a.answer即选项id
            dict_opt[a.answer][1] += 1
    # 计算选择比例
    # 计算填写该题的总人数,若填写人数为0则选择比例为’-%‘
    total = len(list(ans.exclude(answer=None).values_list('submitId', flat=True).distinct()))
    for v in dict_opt.values():
        if total != 0:
            v[2] = ("%.2f" % (v[1] * 100 / total)) + '%'
        else:
            v[2] = '-%'
    return dict_opt


# 统计单个题目的平均完成时间

# 填空题统计
# 总字数统计, 传入某一道题的id
def totalWordCount(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    total = 0
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted).values_list('answerText', flat=True)
    for text in ans:
        total += len(text)
    return total


# 平均数统计
def avgWordCount(qid, ls_submitted):
    total_word = totalWordCount(qid, ls_submitted)
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted).count()
    if ans != 0:
        return "%.2f" % (total_word / ans)
    else:
        return '-'


# 中位数统计
def medianWordCount(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted).values_list('answerText', flat=True)
    word_count = []
    for text in ans:
        word_count.append(len(text))
    word_count.sort()

    if len(word_count) != 0:
        if len(word_count) % 2 != 0:
            return word_count[len(word_count) // 2]
        return (word_count[len(word_count) // 2] + word_count[len(word_count) // 2 - 1]) / 2
    return '-'


# 直方图数据统计
# 依据文本字数上限和最少字数（一般为1、0）计算出极差，开方计算出分组数量k，计算组距，分组（建立k个列表？），计算各组样本数
# 传给前端【区间划分】和【对应样本数】
def wordCountHistogram_ex(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    # 获得该题全部数据（字数）
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted).values_list('answerText', flat=True)
    word_count = []
    for text in ans:
        word_count.append(len(text))
    word_count.sort()
    if len(word_count) == 0:
        return '-'
    # 获取文本字数上下限，并计算出极差
    r = word_count[-1] - word_count[0]
    k = math.floor(math.sqrt(r)) + 1
    d = r // k
    dict_his = {}
    for i in range(k):
        if i == k - 1:
            dict_his[str((word_count[0] + i * d, word_count[0] + (i + 1) * d))] = countSample(word_count,
                                                                                              word_count[0] + i * d,
                                                                                              word_count[-1] + 1)
        else:
            dict_his[str((word_count[0] + i * d, word_count[0] + (i + 1) * d))] = countSample(word_count,
                                                                                              word_count[0] + i * d,
                                                                                              word_count[0] + (i + 1) * d)
    return dict_his


def gradeCount(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    # 找出所有回答
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted)
    # 找出评分题
    grade = GradeQuestionAttribute.objects.filter(questionId=qid).first()
    dict_grade = {}
    # 初始化, 键：值 = 选项id:[选项内容, 选择人数, 比例]  选项id为0——(range-1)
    for i in range(0, grade.range):
        dict_grade[i] = [grade.start + i, 0, '']
    for a in ans:
        # 对异常的选择结果进行忽略处理
        if a and ((a.answer - grade.start) in dict_grade.keys()):
            dict_grade[a.answer-grade.start][1] += 1
    # 计算选择比例
    # 计算填写该题的总人数
    total = len(list(ans.exclude(answer=None).values_list('submitId', flat=True).distinct()))
    for v in dict_grade.values():
        if total != 0:
            v[2] = v[2] = ("%.2f" % (v[1] * 100 / total)) + '%'
        else:
            v[2] = '-%'
    return dict_grade


# 统计评分中位数
def medianGrade(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted)
    grade_count = []
    for a in ans:
        grade_count.append(a.answer)
    grade_count.sort()

    if len(grade_count) != 0:
        if len(grade_count) % 2 == 0:
            return (grade_count[len(grade_count) // 2] + grade_count[len(grade_count) // 2 - 1]) / 2
        return grade_count[len(grade_count) // 2]
    return '-'


def avgGradeCount(qid, ls_submitted):
    # # 找出所有有效提交的submitId
    # ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True))
    ans = Answer.objects.filter(questionId=qid).filter(submitId__in=ls_submitted)
    grade_count = []
    for a in ans:
        grade_count.append(a.answer)
    total_grade = sum(grade_count)

    if len(grade_count) != 0:
        return "%.2f" % (total_grade / len(grade_count))
    return '-'


@api_view(['POST', ])
def exportStatistics(request):
    wid = request.data.get('qid')
    # 找出所有有效提交的submitId
    ls_submitted = list(Submit.objects.filter(wjId=wid).filter(is_submitted=True).values_list('id', flat=True).distinct())

    # filename = 'media//' + str(wid) + '_statistics.xls'
    value = signing.dumps({'questionnaire_id': wid})
    filename = 'media//' + value + '_statistics.xls'
    write_book = xlwt.Workbook()  # 打开一个excel
    # 在打开的excel中添加2个sheet
    sheet1 = write_book.add_sheet('statistic_questionnaire', cell_overwrite_ok=True)
    sheet2 = write_book.add_sheet('statistic_questions', cell_overwrite_ok=True)

    sheet1.write(0, 0, '问卷总份数')
    sheet1.write(0, 1, str(totalQuestionnaire(wid)))
    sheet1.write(1, 0, '填写问卷平均时间/秒')
    sheet1.write(1, 1, str(avgTimeToQuestionnaire_ex(wid)))

    sheet1.write(2, 0, '填写问卷时间分布直方图数据')
    tmp_dic = timeHistogram(wid)
    i = 3
    for k, v in tmp_dic.items():
        sheet1.write(i, 0, k)
        sheet1.write(i, 1, str(v))
        i += 1
    tmp_dic.clear()

    # 获取本问卷的所有题目的qid
    questions = Question.objects.filter(wjId=wid)
    # 每道题的统计数据(问题id：所有统计数据项(同样是字典(数据项名称：数据分布)))
    i = 0
    for q in questions:
        sheet2.write(i, 0, '题目序号')
        sheet2.write(i, 1, str(q.id))
        i += 1
        # 本题填写率
        # dict_q = {'title': q.title, 'type': q.type, 'filling_rate': getFillingRate(q.id)}
        dict_type = {
            'single': '单选题',
            'multi': '多选题',
            'fill': '填空题',
            'grade': '评分题'
        }
        sheet2.write(i, 0, '[' + dict_type[q.type] + ']' + q.title)     # eg.[单选题]1月有多少天？
        sheet2.write(i, 1, '作答率(%)')
        sheet2.write(i, 2, str(getFillingRate(q, ls_submitted)))
        i += 2
        # 针对不同题型进行不同分析
        if q.type in ['single', 'multi']:
            tmp_dic = optionCount(q.id, ls_submitted)
            sheet2.write(i, 0, '选项')
            sheet2.write(i, 1, '选择人数')
            sheet2.write(i, 2, '选择人数占比(%)')
            i += 1
            for k, v in tmp_dic.items():
                sheet2.write(i, 0, v[0])
                sheet2.write(i, 1, str(v[1]))
                sheet2.write(i, 2, str(v[2])[:-1])  # 去掉%
                i += 1
            tmp_dic.clear()
            i += 1
        elif q.type == 'fill':
            sheet2.write(i, 0, '总字数')
            sheet2.write(i, 1, '平均字数')
            sheet2.write(i, 2, '字数中位数')
            sheet2.write(i + 1, 0, str(totalWordCount(q.id, ls_submitted)))
            sheet2.write(i + 1, 1, str(avgWordCount(q.id, ls_submitted)))
            sheet2.write(i + 1, 2, str(medianWordCount(q.id, ls_submitted)))
            # 直方图数据
            tmp_dic = wordCountHistogram_ex(q.id, ls_submitted)
            sheet2.write(i, 3, '字数分布直方图数据')
            i += 1
            for k, v in tmp_dic.items():
                sheet2.write(i, 3, k)
                sheet2.write(i, 4, str(v))
                i += 1
            tmp_dic.clear()
            i += 1
        elif q.type == 'grade':
            sheet2.write(i, 0, '平均分数')
            sheet2.write(i, 1, '分数中位数')
            i += 1
            sheet2.write(i, 0, str(avgGradeCount(q.id, ls_submitted)))
            sheet2.write(i, 1, str(medianGrade(q.id, ls_submitted)))
            i += 1
            sheet2.write(i, 0, '分数项分布统计')
            i += 1
            tmp_dic = gradeCount(q.id, ls_submitted)
            sheet2.write(i, 0, '分数')
            sheet2.write(i, 1, '选择人数')
            sheet2.write(i, 2, '选择人数占比(%)')
            i += 1
            for k, v in tmp_dic.items():
                sheet2.write(i, 0, v[0])
                sheet2.write(i, 1, str(v[1]))
                sheet2.write(i, 2, str(v[2])[:-1])
                i += 1
            tmp_dic.clear()
            i += 1

    write_book.save(filename)
    # return Response({'code': 0, 'msg': 'success', 'path': DEFAULT_URL + 'media/' + str(wid) + '_statistics.xls'}, status=status.HTTP_200_OK)
    return Response({'code': 0, 'msg': 'success', 'path': DEFAULT_URL + 'media/' + value + '_statistics.xls'},
                    status=status.HTTP_200_OK)


@api_view(['POST', ])
def exportQuestionnaire(request):
    questionnaire_id = request.data.get('questionnaire_id')
    value = signing.dumps({'questionnaire_id': questionnaire_id})
    form = request.data.get('form')
    if questionnaire_id is None or form is None:
        return Response({'code': -1, 'msg': '缺少参数'}, status=status.HTTP_400_BAD_REQUEST)
    if Questionnaire.objects.filter(id=questionnaire_id).count() == 0:
        return Response({'code': -1, 'msg': '不存在的问卷id'}, status=status.HTTP_400_BAD_REQUEST)
    questionnaire = Questionnaire.objects.get(id=questionnaire_id)
    document = Document()
    document.styles['Normal'].font.name = '微软雅黑'
    document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '微软雅黑')
    questionnaire_title = document.add_paragraph().add_run(questionnaire.title)
    questionnaire_title.font.size = Pt(36)
    questionnaire_title.font.color.rgb = RGBColor(69, 134, 148)
    questionnaire_desc = document.add_paragraph().add_run(questionnaire.desc)
    questionnaire_desc.font.size = Pt(8)

    questions = Question.objects.filter(wjId=questionnaire_id)
    for question in questions:
        if question.must:
            t = question.title + '(' + '必填' + ')'
        else:
            t = question.title + '(' + '非必填' + ')'
        question_title = document.add_paragraph().add_run(t)
        if question.remark != '':
            question_remark = document.add_paragraph().add_run(question.remark)
            question_remark.font.size = Pt(8)
        question_title.font.size = Pt(13)
        if question.type == 'single' or question.type == 'multi':
            options = Options.objects.filter(questionId=question.id)
            for option in options:
                if question.type == 'single':
                    option_title = document.add_paragraph(style='ListBullet').add_run(option.title)
                    option_title.font.size = Pt(10)
                else:
                    title = '♦    ' + option.title
                    option_title = document.add_paragraph(style=None).add_run(title)
                    option_title.font.size = Pt(10)
        elif question.type == 'fill':
            if question.row == 1:
                document.add_paragraph().add_run('________________________________________')
            else:
                document.add_paragraph().add_run('________________________________________')
                document.add_paragraph().add_run('________________________________________')
        elif question.type == 'grade':
            g = GradeQuestionAttribute.objects.get(questionId=question.id)
            info = g.degree_min + '⭐' * g.range + g.degree_max
            line = document.add_paragraph().add_run(info)
    doc_name = 'media/word/' + value + '.docx'
    document.save(doc_name)
    if form == 'docx':
        return Response({'msg': '导出成功', 'code': 0, 'url': DEFAULT_URL + doc_name}, status=status.HTTP_200_OK)
    # else:
    #     dir_path = dirname(dirname(abspath(__file__)))
    #     print('上级目录绝对路径:', dir_path)
    #     pdf_path = dir_path + '/media/word/' + str(questionnaire_id) + '.pdf'
    #     doc_path = dir_path + '/media/word/' + str(questionnaire_id) + '.docx'
    #     pythoncom.CoInitialize()
    #     word = gencache.EnsureDispatch('Word.Application')
    #     doc = word.Documents.Open(doc_path, ReadOnly=1)
    #     doc.ExportAsFixedFormat(pdf_path,
    #                             constants.wdExportFormatPDF,
    #                             Item=constants.wdExportDocumentWithMarkup,
    #                             CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
    #     word.Quit(constants.wdDoNotSaveChanges)
    #     pdf_name = 'media/word/' + str(questionnaire_id) + '.pdf'
    #     Response({'msg': '导出成功', 'code': 0, 'url': DEFAULT_URL + pdf_name}, status=status.HTTP_200_OK)
